USE master
GO
/* Create database
-------------------------------------------------------------------------------*/
CREATE DATABASE HandmadeShop
GO
USE HandmadeShop
GO
CREATE TABLE dbo.Accounts
(
	Id				UNIQUEIDENTIFIER		NOT NULL		PRIMARY KEY
	,Email			VARCHAR(254)			NOT NULL
	,[Password]		VARCHAR(128)			NOT NULL
	,Phone			VARCHAR(20)				NULL	
	,CreatedDate	DATETIME				NOT NULL
	,CreatedBy		UNIQUEIDENTIFIER		NULL
	,ModifiedDate	DATETIME				NOT NULL
	,ModifiedBy		UNIQUEIDENTIFIER		NOT NULL
	,[Role]			VARCHAR(20)				NOT NULL		DEFAULT('User')
)
GO

/* Create tables
-------------------------------------------------------------------------------*/
CREATE TABLE dbo.Products
(
	Id				UNIQUEIDENTIFIER		NOT NULL		PRIMARY KEY
	,Code			VARCHAR(20)				NOT NULL
	,Name			NVARCHAR(50)			NOT NULL
	,[Description]	NVARCHAR(150)			NULL
	,Instruction	TEXT					NULL
	,Image1			NVARCHAR(256)			NOT NULL
	,Image2			NVARCHAR(256)			NULL
	,Image3			NVARCHAR(256)			NULL
	,Image4			NVARCHAR(256)			NULL
	,Image5			NVARCHAR(256)			NULL
	,ProductTypeId	UNIQUEIDENTIFIER		NOT NULL
	,CreatedDate	DATETIME				NOT NULL
	,CreatedBy		UNIQUEIDENTIFIER		NOT NULL
	,ModifiedDate	DATETIME				NOT NULL
	,ModifiedBy		UNIQUEIDENTIFIER		NOT NULL
)
GO
CREATE TABLE dbo.ProductTypes
(
	Id				UNIQUEIDENTIFIER		NOT NULL		PRIMARY KEY
	,Code			VARCHAR(20)				NOT NULL
	,Name			NVARCHAR(50)			NOT NULL
	,CreatedDate	DATETIME				NOT NULL
	,CreatedBy		UNIQUEIDENTIFIER		NOT NULL
	,ModifiedDate	DATETIME				NOT NULL
	,ModifiedBy		UNIQUEIDENTIFIER		NOT NULL
)
GO
CREATE TABLE dbo.ProductPrices
(
	Id				UNIQUEIDENTIFIER		NOT NULL		PRIMARY KEY
	,ProductId		UNIQUEIDENTIFIER		NOT NULL
	,Price			DECIMAL					NOT NULL
	,CreatedDate	DATETIME				NOT NULL
	,CreatedBy		UNIQUEIDENTIFIER		NOT NULL
)

/* Create relationships
-------------------------------------------------------------------------------*/
ALTER TABLE dbo.Products
ADD CONSTRAINT fk_Product_ProductType FOREIGN KEY(ProductTypeId) REFERENCES dbo.ProductTypes(Id)
GO
ALTER TABLE dbo.ProductPrices
ADD CONSTRAINT fk_ProductPrice_Product FOREIGN KEY(ProductId) REFERENCES dbo.Products(Id)